package org.dfzt.modules.online.cgreport.util;

import java.text.MessageFormat;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;

import org.dfzt.common.system.query.QueryGenerator;
import org.dfzt.common.util.oConvertUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class SqlUtil {
    private static final Logger m = LoggerFactory.getLogger(SqlUtil.class);


    public static final String a = "select * from ( {0}) sel_tab00 limit {1},{2}";


    public static final String b = "select * from ( {0}) sel_tab00 limit {2} offset {1}";


    public static final String c = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}";


    public static final String d = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}";


    public static final String e = "select distinct table_name from information_schema.columns where table_schema = {0}";


    public static final String f = "SELECT distinct c.relname AS  table_name FROM pg_class c";


    public static final String g = "select distinct colstable.table_name as  table_name from user_tab_cols colstable";

    public static final String h = "select distinct c.name as  table_name from sys.objects c";

    public static final String i = "select column_name from information_schema.columns where table_name = {0} and table_schema = {1}";

    public static final String j = "select table_name from information_schema.columns where table_name = {0}";

    public static final String k = "select column_name from all_tab_columns where table_name ={0}";

    public static final String l = "select name from syscolumns where id={0}";


    public static String a(String paramString, Map paramMap) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT t.* FROM ( ");
        stringBuilder.append(paramString + " ");
        stringBuilder.append(") t ");
        if (paramMap != null && paramMap.size() >= 1) {
            stringBuilder.append("WHERE 1=1  ");
            Iterator iterator = paramMap.keySet().iterator();
            while (iterator.hasNext()) {
                String str1 = String.valueOf(iterator.next());
                String str2 = String.valueOf(paramMap.get(str1));
                if (oConvertUtils.isNotEmpty(str2)) {
                    stringBuilder.append(" AND ");
                    stringBuilder.append(" " + str1 + str2);
                }
            }
        }
        return stringBuilder.toString();
    }


    public static String b(String paramString, Map paramMap) {
        String a1 = a(paramString, paramMap);
        return "SELECT COUNT(*) COUNT FROM (" + a1 + ") t2";
    }


    public static String a(String paramString, Map paramMap, int paramInt1, int paramInt2) {
        String str = "jdbc:mysql://127.0.0.1:3306/jeecg-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false";
        int n = (paramInt1 - 1) * paramInt2;
        String[] arrayOfString = new String[3];
        arrayOfString[0] = paramString;
        arrayOfString[1] = n + "";
        arrayOfString[2] = paramInt2 + "";
        if (str.indexOf("MYSQL") != -1) {
            paramString = MessageFormat.format("select * from ( {0}) sel_tab00 limit {1},{2}", arrayOfString);
        } else if (str.indexOf("POSTGRESQL") != -1) {
            paramString = MessageFormat.format("select * from ( {0}) sel_tab00 limit {2} offset {1}", arrayOfString);
        } else {
            int i1 = (paramInt1 - 1) * paramInt2;
            int i2 = i1 + paramInt2;
            arrayOfString[2] = Integer.toString(i1);
            arrayOfString[1] = Integer.toString(i2);
            if (str.indexOf("ORACLE") != -1) {
                paramString = MessageFormat.format("select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}", arrayOfString);
            } else if (str.indexOf("SQLSERVER") != -1) {
                arrayOfString[0] = paramString.substring(c(paramString));
                paramString = MessageFormat.format("select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}", arrayOfString);
            }
        }
        return paramString;
    }


    public static String a(String paramString1, String paramString2, String paramString3, Map paramMap, int paramInt1, int paramInt2) {
        paramString3 = a(paramString3, paramMap);
        int n = (paramInt1 - 1) * paramInt2;
        String[] arrayOfString = new String[3];
        arrayOfString[0] = paramString3;
        arrayOfString[1] = n + "";
        arrayOfString[2] = paramInt2 + "";

        String str = "";
        if ("MYSQL".equalsIgnoreCase(str)) {
            paramString3 = MessageFormat.format("select * from ( {0}) sel_tab00 limit {1},{2}", arrayOfString);
        } else if ("POSTGRESQL".equalsIgnoreCase(str)) {
            paramString3 = MessageFormat.format("select * from ( {0}) sel_tab00 limit {2} offset {1}", arrayOfString);
        } else {
            int i1 = (paramInt1 - 1) * paramInt2;
            int i2 = i1 + paramInt2;
            arrayOfString[2] = Integer.toString(i1);
            arrayOfString[1] = Integer.toString(i2);
            if ("ORACLE".equalsIgnoreCase(str)) {
                paramString3 = MessageFormat.format("select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}", arrayOfString);
            } else if ("SQLSERVER".equalsIgnoreCase(str)) {
                arrayOfString[0] = paramString3.substring(c(paramString3));
                paramString3 = MessageFormat.format("select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}", arrayOfString);
            }
        }
        return paramString3;
    }

    private static int c(String paramString) {
        int n = paramString.toLowerCase().indexOf("select");
        int i1 = paramString.toLowerCase().indexOf("select distinct");
        return n + ((i1 == n) ? 15 : 6);
    }


    public static String a(String paramString, String... paramVarArgs) {
        if (oConvertUtils.isNotEmpty(paramString)) {
            if ("MYSQL".equals(paramString))
                return MessageFormat.format("select distinct table_name from information_schema.columns where table_schema = {0}", paramVarArgs);
            if ("ORACLE".equals(paramString))
                return "select distinct colstable.table_name as  table_name from user_tab_cols colstable";
            if ("POSTGRESQL".equals(paramString))
                return "SELECT distinct c.relname AS  table_name FROM pg_class c";
            if ("SQLSERVER".equals(paramString)) {
                return "select distinct c.name as  table_name from sys.objects c";
            }
        }
        return null;
    }

    public static String b(String paramString, String... paramVarArgs) {
        if (oConvertUtils.isNotEmpty(paramString)) {
            if ("MYSQL".equals(paramString))
                return MessageFormat.format("select column_name from information_schema.columns where table_name = {0} and table_schema = {1}", paramVarArgs);
            if ("ORACLE".equals(paramString))
                return MessageFormat.format("select column_name from all_tab_columns where table_name ={0}", paramVarArgs);
            if ("POSTGRESQL".equals(paramString))
                return MessageFormat.format("select table_name from information_schema.columns where table_name = {0}", paramVarArgs);
            if ("SQLSERVER".equals(paramString)) {
                return MessageFormat.format("select name from syscolumns where id={0}", paramVarArgs);
            }
        }
        return null;
    }


    public static String a(String paramString) {
        paramString = paramString.toLowerCase();
        String str = "(\\w|\\.)+ *\\S+ *\\S*\\$\\{\\w+\\}\\S*";
        Pattern pattern = Pattern.compile(str);
        Matcher matcher = pattern.matcher(paramString);
        while (matcher.find()) {
            String str1 = matcher.group();
            m.info("${}匹配带参SQL片段 ==>" + str1);
            if (str1.indexOf("where") != -1) {
                paramString = paramString.replace(str1, "where 1=1");
            } else if (str1.indexOf("and") != -1) {
                paramString = paramString.replace(str1, "and 1=1");
            } else if (str1.indexOf("or") != -1) {
                paramString = paramString.replace(str1, "or 1=1");
            } else {
                paramString = paramString.replace(str1, "1=1");
            }

            m.info("${}替换后结果 ==>" + paramString);
        }


        return paramString.replaceAll("(?i)AND *1=1", "");
    }


    public static void main(String[] args) {
        String str = "select * from sys_user where id   ='${id}' and del_flag=  ${flag}";
        System.out.println(a(str));
    }


    public static Map<String, Object> a(HttpServletRequest paramHttpServletRequest) {
        Map map = paramHttpServletRequest.getParameterMap();

        HashMap hashMap = new HashMap();
        Iterator iterator = map.entrySet().iterator();


        String str1 = "";
        String str2 = "";
        Object object = null;
        while (iterator.hasNext()) {
            Map.Entry entry = (Map.Entry) iterator.next();
            str1 = (String) entry.getKey();
            object = entry.getValue();
            if ("_t".equals(str1) || null == object) {
                str2 = "";
            } else if (object instanceof String[]) {
                String[] arrayOfString = (String[]) object;
                for (byte b1 = 0; b1 < arrayOfString.length; b1++) {
                    str2 = arrayOfString[b1] + ",";
                }
                str2 = str2.substring(0, str2.length() - 1);
            } else {
                str2 = object.toString();
            }
            hashMap.put(str1, str2);
        }
        return hashMap;
    }


    public static String b(String paramString) {
        String str1 = QueryGenerator.convertSystemVariables(paramString);
        String str2 = QueryGenerator.getAllConfigAuth();
        if (paramString.toLowerCase().indexOf("where") > 0) {
            return str1 + str2;
        }
        return str1 + " where 1=1  " + str2;
    }
}
